﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CommunitySystemModel.AdvancedCustomerQuery;
using System.Xml.Linq;
using System.Data;

namespace CommunitySystem.BO.AdvancedCustomerQuery
{
    public class ZWSPBQKBO
    {
        public string QueryData(Query_CustomerQuery item, string orgids, int Year)
        {
            int RowNumbers = 4;
            int ColNumbers = 24;
            string[] orgs = orgids.Split(',');
            XDocument xdoc = new XDocument(new XDeclaration("1.0", "utf-8", "yes"));
            XElement root = new XElement("root", //new XAttribute("rownumbers", RowNumbers),
                    new XAttribute("colnumbers", ColNumbers),
                    new XAttribute("maxrowlevel", 3)
                    , new XAttribute("maxcollevel", 1));
            XElement rows = new XElement("rows");
            int rowIndex = 0;
            int colIndex = 0;
            #region 前3行
            #region 第0行
            XElement row0 = new XElement("row", new XAttribute("rowindex", rowIndex));
            colIndex = 0;
            XElement cell00 = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                                   new XAttribute("value", "单位名称"),
                                                   new XAttribute("sql", ""),
                                                   new XAttribute("col", colIndex),
                                                   new XAttribute("row", 0),
                                                   new XAttribute("rowspan", 3),
                                                   new XAttribute("colspan", 1));
            colIndex++;
            row0.Add(cell00);
            XElement cell01 = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                                   new XAttribute("value", "核定编制数"),
                                                   new XAttribute("sql", ""),
                                                   new XAttribute("col", colIndex),
                                                   new XAttribute("row", 0),
                                                   new XAttribute("rowspan", 3),
                                                   new XAttribute("colspan", 1));
            colIndex++;
            row0.Add(cell01);
            XElement cell02 = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                                  new XAttribute("value", "空编数"),
                                                  new XAttribute("sql", ""),
                                                  new XAttribute("col", colIndex),
                                                  new XAttribute("row", 0),
                                                  new XAttribute("rowspan", 3),
                                                  new XAttribute("colspan", 1));
            colIndex++;
            row0.Add(cell02);
            XElement cell03 = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                                 new XAttribute("value", "科级及以下干部实有人数"),
                                                 new XAttribute("sql", ""),
                                                 new XAttribute("col", colIndex),
                                                 new XAttribute("row", 0),
                                                 new XAttribute("rowspan", 3),
                                                 new XAttribute("colspan", 1));
            colIndex++;
            row0.Add(cell03);
            XElement cell04 = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                                 new XAttribute("value", "核定科级领导职数及实际配备人数"),
                                                 new XAttribute("sql", ""),
                                                 new XAttribute("col", colIndex),
                                                 new XAttribute("row", 0),
                                                 new XAttribute("rowspan", 1),
                                                 new XAttribute("colspan", 4));
            colIndex+=4;
            row0.Add(cell04);
            XElement cell05 = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                                new XAttribute("value", "核定科级非领导职数及实际配备人数"),
                                                new XAttribute("sql", ""),
                                                new XAttribute("col", colIndex),
                                                new XAttribute("row", 0),
                                                new XAttribute("rowspan", 1),
                                                new XAttribute("colspan", 4));
            colIndex+=4;
            row0.Add(cell05);
            XElement cell06 = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                               new XAttribute("value", "科员实有人数"),
                                               new XAttribute("sql", ""),
                                               new XAttribute("col", colIndex),
                                               new XAttribute("row", 0),
                                               new XAttribute("rowspan", 3),
                                               new XAttribute("colspan", 1));
            colIndex++;
            row0.Add(cell06);
            XElement cell07 = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                               new XAttribute("value", "试用期人员实有人数"),
                                               new XAttribute("sql", ""),
                                               new XAttribute("col", colIndex),
                                               new XAttribute("row", 0),
                                               new XAttribute("rowspan", 3),
                                               new XAttribute("colspan", 1));
            colIndex++;
            row0.Add(cell07);
            XElement cell08 = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                               new XAttribute("value", "进本单位已满15年人数"),
                                               new XAttribute("sql", ""),
                                               new XAttribute("col", colIndex),
                                               new XAttribute("row", 0),
                                               new XAttribute("rowspan", 3),
                                               new XAttribute("colspan", 1));
            colIndex++;
            row0.Add(cell08);
            XElement cell09 = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                               new XAttribute("value", Year+"年退休科级及科以下干部人数"),
                                               new XAttribute("sql", ""),
                                               new XAttribute("col", colIndex),
                                               new XAttribute("row", 0),
                                               new XAttribute("rowspan", 2),
                                               new XAttribute("colspan", 3));
            colIndex+=3;
            row0.Add(cell09);
            XElement cell10 = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                               new XAttribute("value", (Year +1) + "年退休科级及科以下干部人数"),
                                               new XAttribute("sql", ""),
                                               new XAttribute("col", colIndex),
                                               new XAttribute("row", 0),
                                               new XAttribute("rowspan", 2),
                                               new XAttribute("colspan", 3));
            colIndex+=3;
            row0.Add(cell10);
            XElement cell11 = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                               new XAttribute("value", (Year + 2) + "年退休科级及科以下干部人数"),
                                               new XAttribute("sql", ""),
                                               new XAttribute("col", colIndex),
                                               new XAttribute("row", 0),
                                               new XAttribute("rowspan", 2),
                                               new XAttribute("colspan", 3));
            colIndex+=3;
            row0.Add(cell11);
            rows.Add(row0);
            rowIndex++;
            #endregion
            #region 第1行
            XElement row = new XElement("row", new XAttribute("rowindex", rowIndex));
            colIndex = 4;
            XElement cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                                   new XAttribute("value", "领导职数合计"),
                                                   new XAttribute("sql", ""),
                                                   new XAttribute("col", colIndex),
                                                   new XAttribute("row", rowIndex),
                                                   new XAttribute("rowspan", 2),
                                                   new XAttribute("colspan", 1));
            row.Add(cell);
            colIndex++;
            
             cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                                   new XAttribute("value", "科长"),
                                                   new XAttribute("sql", ""),
                                                   new XAttribute("col", colIndex),
                                                   new XAttribute("row", rowIndex),
                                                   new XAttribute("rowspan", 1),
                                                   new XAttribute("colspan", 1));
            row.Add(cell);           
            colIndex++;

            cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                                   new XAttribute("value", "副科长"),
                                                   new XAttribute("sql", ""),
                                                   new XAttribute("col", colIndex),
                                                   new XAttribute("row", rowIndex),
                                                   new XAttribute("rowspan", 1),
                                                   new XAttribute("colspan", 1));
            row.Add(cell);
            colIndex ++;

            cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                                  new XAttribute("value", "空缺数"),
                                                  new XAttribute("sql", ""),
                                                  new XAttribute("col", colIndex),
                                                  new XAttribute("row", rowIndex),
                                                  new XAttribute("rowspan", 2),
                                                  new XAttribute("colspan", 1));
            row.Add(cell);
            colIndex++;

            cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                                   new XAttribute("value", "非领导职数合计"),
                                                   new XAttribute("sql", ""),
                                                   new XAttribute("col", colIndex),
                                                   new XAttribute("row", rowIndex),
                                                   new XAttribute("rowspan", 2),
                                                   new XAttribute("colspan", 1));
            row.Add(cell);
            colIndex++;

            
            cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                                   new XAttribute("value", "主任科员"),
                                                   new XAttribute("sql", ""),
                                                   new XAttribute("col", colIndex),
                                                   new XAttribute("row", rowIndex),
                                                   new XAttribute("rowspan", 1),
                                                   new XAttribute("colspan", 1));
            row.Add(cell);
            colIndex ++;
            cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                                   new XAttribute("value", "副主任科员"),
                                                   new XAttribute("sql", ""),
                                                   new XAttribute("col", colIndex),
                                                   new XAttribute("row", rowIndex),
                                                   new XAttribute("rowspan", 1),
                                                   new XAttribute("colspan", 1));
            row.Add(cell);
            colIndex++;


            cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                                 new XAttribute("value", "空缺数"),
                                                 new XAttribute("sql", ""),
                                                 new XAttribute("col", colIndex),
                                                 new XAttribute("row", rowIndex),
                                                 new XAttribute("rowspan", 2),
                                                 new XAttribute("colspan", 1));
            row.Add(cell);
            colIndex++;


            rows.Add(row);
            rowIndex++;
            #endregion
            #region 第2行
            row = new XElement("row", new XAttribute("rowindex", rowIndex));
            colIndex = 5;
            cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                                  new XAttribute("value", "实有人数"),
                                                  new XAttribute("sql", ""),
                                                  new XAttribute("col", colIndex),
                                                  new XAttribute("row", rowIndex),
                                                  new XAttribute("rowspan", 1),              
                                                  new XAttribute("colspan", 1));
            colIndex++;
            row.Add(cell);
            

            cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                                 new XAttribute("value", "实有人数"),
                                                 new XAttribute("sql", ""),
                                                 new XAttribute("col", colIndex),
                                                 new XAttribute("row", rowIndex),
                                                 new XAttribute("rowspan", 1),
                                                 new XAttribute("colspan", 1));
            colIndex++;
            row.Add(cell);
            colIndex += 2;

            cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                                 new XAttribute("value", "实有人数"),
                                                 new XAttribute("sql", ""),
                                                 new XAttribute("col", colIndex),
                                                 new XAttribute("row", rowIndex),
                                                 new XAttribute("rowspan", 1),
                                                 new XAttribute("colspan", 1));
            colIndex++;
            row.Add(cell);

            cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                                new XAttribute("value", "实有人数"),
                                                new XAttribute("sql", ""),
                                                new XAttribute("col", colIndex),
                                                new XAttribute("row", rowIndex),
                                                new XAttribute("rowspan", 1),
                                                new XAttribute("colspan", 1));
            colIndex++;
            row.Add(cell);

            colIndex+=4;

            cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                                new XAttribute("value", "科级领导职务"),
                                                new XAttribute("sql", ""),
                                                new XAttribute("col", colIndex),
                                                new XAttribute("row", rowIndex),
                                                new XAttribute("rowspan", 1),
                                                new XAttribute("colspan", 1));
            colIndex++;
            row.Add(cell);

            cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                                new XAttribute("value", "科级非领导职务"),
                                                new XAttribute("sql", ""),
                                                new XAttribute("col", colIndex),
                                                new XAttribute("row", rowIndex),
                                                new XAttribute("rowspan", 1),
                                                new XAttribute("colspan", 1));
            colIndex++;
            row.Add(cell);
            cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                                new XAttribute("value", "科员"),
                                                new XAttribute("sql", ""),
                                                new XAttribute("col", colIndex),
                                                new XAttribute("row", rowIndex),
                                                new XAttribute("rowspan", 1),
                                                new XAttribute("colspan", 1));
            colIndex++;
            row.Add(cell);

            cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                                new XAttribute("value", "科级领导职务"),
                                                new XAttribute("sql", ""),
                                                new XAttribute("col", colIndex),
                                                new XAttribute("row", rowIndex),
                                                new XAttribute("rowspan", 1),
                                                new XAttribute("colspan", 1));
            colIndex++;
            row.Add(cell);

            cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                                new XAttribute("value", "科级非领导职务"),
                                                new XAttribute("sql", ""),
                                                new XAttribute("col", colIndex),
                                                new XAttribute("row", rowIndex),
                                                new XAttribute("rowspan", 1),
                                                new XAttribute("colspan", 1));
            colIndex++;
            row.Add(cell);
            cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                                new XAttribute("value", "科员"),
                                                new XAttribute("sql", ""),
                                                new XAttribute("col", colIndex),
                                                new XAttribute("row", rowIndex),
                                                new XAttribute("rowspan", 1),
                                                new XAttribute("colspan", 1));
            colIndex++;
            row.Add(cell);

            cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                                new XAttribute("value", "科级领导职务"),
                                                new XAttribute("sql", ""),
                                                new XAttribute("col", colIndex),
                                                new XAttribute("row", rowIndex),
                                                new XAttribute("rowspan", 1),
                                                new XAttribute("colspan", 1));
            colIndex++;
            row.Add(cell);

            cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                                new XAttribute("value", "科级非领导职务"),
                                                new XAttribute("sql", ""),
                                                new XAttribute("col", colIndex),
                                                new XAttribute("row", rowIndex),
                                                new XAttribute("rowspan", 1),
                                                new XAttribute("colspan", 1));
            colIndex++;
            row.Add(cell);
            cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                                new XAttribute("value", "科员"),
                                                new XAttribute("sql", ""),
                                                new XAttribute("col", colIndex),
                                                new XAttribute("row", rowIndex),
                                                new XAttribute("rowspan", 1),
                                                new XAttribute("colspan", 1));
            colIndex++;
            row.Add(cell);

            rows.Add(row);
            rowIndex++;
            #endregion
            #endregion


            #region 数据行
            string sql = string.Empty;
            DataSet ds = null;
            if (Year == DateTime.Now.Year)//查询时时数据
            {
                sql = "exec GeneratorLeaderPositionPeiBeiTable 1";
                
            } 
            else//查询历史数据
            {
                sql = string.Format("SELECT * FROM Query_KJJKYXGBZSPBQKDCB WHERE [Year]='{0}' AND OrgID IN ({1})", Year, orgids);
            }
            ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            if (ds.Tables[0].Rows.Count > 0)
            {
                XElement total = new XElement("row");
                RowNumbers++;
                int _HDBZS = 0;
                int _KBS = 0;
                int _KJJKYXGBSYRS = 0;
                string _KJJKYXGBSYRSsql = string.Empty;
                int _LDZSHJ = 0;
                int _KZSYRS = 0;
                string _KZSYRSsql = string.Empty;
                int _FKZSYRS = 0;
                string _FKZSYRSsql = string.Empty;                
                int _LDKQS = 0;


                int _FLDZSHJ = 0;
                int _ZRKYSYRS = 0;
                string _ZRKYSYRSsql = string.Empty;
                int _FZRKYSYRS = 0;
                string _FZRKYSYRSsql = string.Empty;
                int _FLDKQS = 0;

                int _KYSYRS = 0;
                string _KYSYRSsql = string.Empty;
                int _SYQRYSYRS = 0;
                string _SYQRYSYRSsql = string.Empty;
                int _M15N = 0;
                string _M15Nsql = string.Empty;

                int DN1 = 0;
                string DN1sql = string.Empty;
                int DN2 = 0;
                string DN2sql = string.Empty;
                int DN3 = 0;
                string DN3sql = string.Empty;

                int CN1 = 0;
                string CN1sql = string.Empty;
                int CN2 = 0;
                string CN2sql = string.Empty;
                int CN3 = 0;
                string CN3sql = string.Empty;

                int TD1 = 0;
                string TD1sql = string.Empty;
                int TD2 = 0;
                string TD2sql = string.Empty;
                int TD3 = 0;
                string TD3sql = string.Empty;

                #region detail
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    row = new XElement("row", new XAttribute("rowindex", rowIndex));
                    colIndex = 0;
                    if (Year == DateTime.Now.Year)//查询时时数据
                    {
                        string orgid = (int)dr["OrgID"] + "";
                        if (orgs.Where(c => c.Equals(orgid)).Count() <= 0)
                            continue;
                    }
                    RowNumbers++;
                    cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                               new XAttribute("value", dr["OrgName"] as string),
                                               new XAttribute("sql", ""),
                                               new XAttribute("col", colIndex++),
                                               new XAttribute("row", rowIndex),
                                               new XAttribute("rowspan", 1),
                                               new XAttribute("colspan", 1));
                    row.Add(cell);

                    cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                               new XAttribute("value", dr["HDBZS"] as Int32?),
                                               new XAttribute("sql", ""),
                                               new XAttribute("col", colIndex++),
                                               new XAttribute("row", rowIndex),
                                               new XAttribute("rowspan", 1),
                                               new XAttribute("colspan", 1));
                    _HDBZS += (int)dr["HDBZS"];

                    row.Add(cell);
                   

                    cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                               new XAttribute("value", dr["KBS"] as Int32?),
                                               new XAttribute("sql", ""),
                                               new XAttribute("col", colIndex++),
                                               new XAttribute("row", rowIndex),
                                               new XAttribute("rowspan", 1),
                                               new XAttribute("colspan", 1));
                    _KBS += (int)dr["KBS"];
                    row.Add(cell);

                    cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                               new XAttribute("value", dr["KJJKYXGBSYRS"] as Int32?),
                                               new XAttribute("sql", dr["KJJKYXGBSYRSsql"] as string),
                                               new XAttribute("col", colIndex++),
                                               new XAttribute("row", rowIndex),
                                               new XAttribute("rowspan", 1),
                                               new XAttribute("colspan", 1));
                    _KJJKYXGBSYRS += (int)dr["KJJKYXGBSYRS"];
                    _KJJKYXGBSYRSsql += string.IsNullOrEmpty(_KJJKYXGBSYRSsql) ? dr["KJJKYXGBSYRSsql"] as string : " UNION ALL \n\t" + dr["KJJKYXGBSYRSsql"] as string; 
                    row.Add(cell);
                  

                    cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                              new XAttribute("value", dr["LDZSHJ"] as Int32?),
                                              new XAttribute("sql", ""),
                                              new XAttribute("col", colIndex++),
                                              new XAttribute("row", rowIndex),
                                              new XAttribute("rowspan", 1),
                                              new XAttribute("colspan", 1));
                    _LDZSHJ += (int)dr["LDZSHJ"];
                    
                    row.Add(cell);

                    cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                              new XAttribute("value", dr["KZSYRS"] as Int32?),
                                              new XAttribute("sql", dr["KZSYRSsql"] as string),
                                              new XAttribute("col", colIndex++),
                                              new XAttribute("row", rowIndex),
                                              new XAttribute("rowspan", 1),
                                              new XAttribute("colspan", 1));
                    _KZSYRS += (int)dr["KZSYRS"];
                    _KZSYRSsql += string.IsNullOrEmpty(_KZSYRSsql) ? dr["KZSYRSsql"] as string : " UNION ALL \n\t" + dr["KZSYRSsql"] as string; 
                    row.Add(cell);

                    cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                              new XAttribute("value", dr["FKZSYRS"] as Int32?),
                                              new XAttribute("sql", dr["FKZSYRSsql"] as string),
                                              new XAttribute("col", colIndex++),
                                              new XAttribute("row", rowIndex),
                                              new XAttribute("rowspan", 1),
                                              new XAttribute("colspan", 1));
                    _FKZSYRS += (int)dr["FKZSYRS"];
                    _FKZSYRSsql += string.IsNullOrEmpty(_FKZSYRSsql) ? dr["FKZSYRSsql"] as string : " UNION ALL \n\t" + dr["FKZSYRSsql"] as string; 
                    row.Add(cell);

                    cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                              new XAttribute("value", dr["KJLDKQS"] as Int32?),
                                              new XAttribute("sql", ""),
                                              new XAttribute("col", colIndex++),
                                              new XAttribute("row", rowIndex),
                                              new XAttribute("rowspan", 1),
                                              new XAttribute("colspan", 1));
                    _LDKQS += (int)dr["KJLDKQS"];
                    row.Add(cell);

                    cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                              new XAttribute("value", dr["FLDHJ"] as Int32?),
                                              new XAttribute("sql", ""),
                                              new XAttribute("col", colIndex++),
                                              new XAttribute("row", rowIndex),
                                              new XAttribute("rowspan", 1),
                                              new XAttribute("colspan", 1));
                    _FLDZSHJ += (int)dr["FLDHJ"];
                    row.Add(cell);

                    cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                             new XAttribute("value", dr["ZRKYSYRS"] as Int32?),
                                             new XAttribute("sql", dr["ZRKYSYRSsql"] as string),
                                             new XAttribute("col", colIndex++),
                                             new XAttribute("row", rowIndex),
                                             new XAttribute("rowspan", 1),
                                             new XAttribute("colspan", 1));
                    _ZRKYSYRS += (int)dr["ZRKYSYRS"];
                    _ZRKYSYRSsql += string.IsNullOrEmpty(_ZRKYSYRSsql) ? dr["ZRKYSYRSsql"] as string : " UNION ALL \n\t" + dr["ZRKYSYRSsql"] as string; 
                    row.Add(cell);

                    cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                             new XAttribute("value", dr["FZRKYSYRS"] as Int32?),
                                             new XAttribute("sql", dr["FZRKYSYRSsql"] as string),
                                             new XAttribute("col", colIndex++),
                                             new XAttribute("row", rowIndex),
                                             new XAttribute("rowspan", 1),
                                             new XAttribute("colspan", 1));
                    _FZRKYSYRS += (int)dr["FZRKYSYRS"];
                    _FZRKYSYRSsql += string.IsNullOrEmpty(_FZRKYSYRSsql) ? dr["FZRKYSYRSsql"] as string : " UNION ALL \n\t" + dr["FZRKYSYRSsql"] as string; 
                    row.Add(cell);

                    cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                             new XAttribute("value", dr["KJFLDKQS"] as Int32?),
                                             new XAttribute("sql", ""),
                                             new XAttribute("col", colIndex++),
                                             new XAttribute("row", rowIndex),
                                             new XAttribute("rowspan", 1),
                                             new XAttribute("colspan", 1));
                    _FLDKQS += (int)dr["KJFLDKQS"];
                    row.Add(cell);


                    cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                             new XAttribute("value", dr["KYSYRS"] as Int32?),
                                             new XAttribute("sql", dr["KYSYRSsql"] as string),
                                             new XAttribute("col", colIndex++),
                                             new XAttribute("row", rowIndex),
                                             new XAttribute("rowspan", 1),
                                             new XAttribute("colspan", 1));
                    _KYSYRS += (int)dr["KYSYRS"];
                    _KYSYRSsql += string.IsNullOrEmpty(_KYSYRSsql) ? dr["KYSYRSsql"] as string : " UNION ALL \n\t" + dr["KYSYRSsql"] as string; 
                    row.Add(cell);

                    cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                             new XAttribute("value", dr["SYQRYSYRS"] as Int32?),
                                             new XAttribute("sql", dr["SYQRYSYRSsql"] as string),
                                             new XAttribute("col", colIndex++),
                                             new XAttribute("row", rowIndex),
                                             new XAttribute("rowspan", 1),
                                             new XAttribute("colspan", 1));
                    _SYQRYSYRS += (int)dr["SYQRYSYRS"];
                    _SYQRYSYRSsql += string.IsNullOrEmpty(_SYQRYSYRSsql) ? dr["SYQRYSYRSsql"] as string : " UNION ALL \n\t" + dr["SYQRYSYRSsql"] as string; 
                    row.Add(cell);


                    cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                             new XAttribute("value", dr["JBDWYM15NRS"] as Int32?),
                                             new XAttribute("sql", dr["JBDWYM15NRSsql"] as string),
                                             new XAttribute("col", colIndex++),
                                             new XAttribute("row", rowIndex),
                                             new XAttribute("rowspan", 1),
                                             new XAttribute("colspan", 1));
                    _M15N += (int)dr["JBDWYM15NRS"];
                    _M15Nsql += string.IsNullOrEmpty(_M15Nsql) ? dr["JBDWYM15NRSsql"] as string : " UNION ALL \n\t" + dr["JBDWYM15NRSsql"] as string; 
                    row.Add(cell);

                    cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                             new XAttribute("value", dr["DNKJLDZW"] as Int32?),
                                             new XAttribute("sql", dr["DNKJLDZWsql"] as string),
                                             new XAttribute("col", colIndex++),
                                             new XAttribute("row", rowIndex),
                                             new XAttribute("rowspan", 1),
                                             new XAttribute("colspan", 1));
                    DN1 += (int)dr["DNKJLDZW"];
                    DN1sql += string.IsNullOrEmpty(DN1sql) ? dr["DNKJLDZWsql"] as string : " UNION ALL \n\t" + dr["DNKJLDZWsql"] as string; 
                    row.Add(cell);

                    cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                             new XAttribute("value", dr["DNKJFLDZW"] as Int32?),
                                             new XAttribute("sql", dr["DNKJFLDZWsql"] as string),
                                             new XAttribute("col", colIndex++),
                                             new XAttribute("row", rowIndex),
                                             new XAttribute("rowspan", 1),
                                             new XAttribute("colspan", 1));
                    DN2 += (int)dr["DNKJFLDZW"];
                    DN2sql += string.IsNullOrEmpty(DN2sql) ? dr["DNKJFLDZWsql"] as string : " UNION ALL \n\t" + dr["DNKJFLDZWsql"] as string; 
                    row.Add(cell);

                    cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                            new XAttribute("value", dr["DNKY"] as Int32?),
                                            new XAttribute("sql", dr["DNKYsql"] as string),
                                            new XAttribute("col", colIndex++),
                                            new XAttribute("row", rowIndex),
                                            new XAttribute("rowspan", 1),
                                            new XAttribute("colspan", 1));
                    DN3 += (int)dr["DNKY"];
                    DN3sql += string.IsNullOrEmpty(DN3sql) ? dr["DNKYsql"] as string : " UNION ALL \n\t" + dr["DNKYsql"] as string; 
                    row.Add(cell);


                    cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                            new XAttribute("value", dr["CNKJLDZW"] as Int32?),
                                            new XAttribute("sql", dr["CNKJLDZWsql"] as string),
                                            new XAttribute("col", colIndex++),
                                            new XAttribute("row", rowIndex),
                                            new XAttribute("rowspan", 1),
                                            new XAttribute("colspan", 1));
                    CN1 += (int)dr["CNKJLDZW"];
                    CN1sql += string.IsNullOrEmpty(CN1sql) ? dr["CNKJLDZWsql"] as string : " UNION ALL \n\t" + dr["CNKJLDZWsql"] as string; 
                    row.Add(cell);

                    cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                           new XAttribute("value", dr["CNKJFLDZW"] as Int32?),
                                           new XAttribute("sql", dr["CNKJFLDZWsql"] as string),
                                           new XAttribute("col", colIndex++),
                                           new XAttribute("row", rowIndex),
                                           new XAttribute("rowspan", 1),
                                           new XAttribute("colspan", 1));
                    CN2 += (int)dr["CNKJFLDZW"];
                    CN2sql += string.IsNullOrEmpty(CN2sql) ? dr["CNKJFLDZWsql"] as string : " UNION ALL \n\t" + dr["CNKJFLDZWsql"] as string;
                    row.Add(cell);

                    cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                           new XAttribute("value", dr["CNKY"] as Int32?),
                                           new XAttribute("sql", dr["CNKYsql"] as string),
                                           new XAttribute("col", colIndex++),
                                           new XAttribute("row", rowIndex),
                                           new XAttribute("rowspan", 1),
                                           new XAttribute("colspan", 1));
                    CN3 += (int)dr["CNKY"];
                    CN3sql += string.IsNullOrEmpty(CN3sql) ? dr["CNKYsql"] as string : " UNION ALL \n\t" + dr["CNKYsql"] as string;
                    row.Add(cell);


                    cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                          new XAttribute("value", dr["TDKJLDZW"] as Int32?),
                                          new XAttribute("sql", dr["TDKJLDZWsql"] as string),
                                          new XAttribute("col", colIndex++),
                                          new XAttribute("row", rowIndex),
                                          new XAttribute("rowspan", 1),
                                          new XAttribute("colspan", 1));
                    TD1 += (int)dr["TDKJLDZW"];
                    TD1sql += string.IsNullOrEmpty(TD1sql) ? dr["TDKJLDZWsql"] as string : " UNION ALL \n\t" + dr["TDKJLDZWsql"] as string; 
                    row.Add(cell);

                    cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                         new XAttribute("value", dr["TDKJFLDZW"] as Int32?),
                                         new XAttribute("sql", dr["TDKJFLDZWsql"] as string),
                                         new XAttribute("col", colIndex++),
                                         new XAttribute("row", rowIndex),
                                         new XAttribute("rowspan", 1),
                                         new XAttribute("colspan", 1));
                    TD2 += (int)dr["TDKJFLDZW"];
                    TD2sql += string.IsNullOrEmpty(TD2sql) ? dr["TDKJFLDZWsql"] as string : " UNION ALL \n\t" + dr["TDKJFLDZWsql"] as string; 
                    row.Add(cell);

                    cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                         new XAttribute("value", dr["TDKY"] as Int32?),
                                         new XAttribute("sql", dr["TDKYsql"] as string),
                                         new XAttribute("col", colIndex++),
                                         new XAttribute("row", rowIndex),
                                         new XAttribute("rowspan", 1),
                                         new XAttribute("colspan", 1));
                    TD3 += (int)dr["TDKY"];
                    TD3sql += string.IsNullOrEmpty(TD3sql) ? dr["TDKYsql"] as string : " UNION ALL \n\t" + dr["TDKYsql"] as string; 
                    row.Add(cell);

                    rows.Add(row);
                    rowIndex++;
                }
                #endregion

                #region 合计
                colIndex = 0;
                total.Add(new XAttribute("rowindex", rowIndex));

                cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                               new XAttribute("value", "合计"),
                                               new XAttribute("sql", ""),
                                               new XAttribute("col", colIndex++),
                                               new XAttribute("row", rowIndex),
                                               new XAttribute("rowspan", 1),
                                               new XAttribute("colspan", 1));
                total.Add(cell);

                cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                               new XAttribute("value", _HDBZS),
                                               new XAttribute("sql", ""),
                                               new XAttribute("col", colIndex++),
                                               new XAttribute("row", rowIndex),
                                               new XAttribute("rowspan", 1),
                                               new XAttribute("colspan", 1));
                total.Add(cell);

                cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                               new XAttribute("value", _KBS),
                                               new XAttribute("sql", ""),
                                               new XAttribute("col", colIndex++),
                                               new XAttribute("row", rowIndex),
                                               new XAttribute("rowspan", 1),
                                               new XAttribute("colspan", 1));
                total.Add(cell);
                cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                               new XAttribute("value",_KJJKYXGBSYRS),
                                               new XAttribute("sql", _KJJKYXGBSYRSsql),
                                               new XAttribute("col", colIndex++),
                                               new XAttribute("row", rowIndex),
                                               new XAttribute("rowspan", 1),
                                               new XAttribute("colspan", 1));
                total.Add(cell);

                cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                               new XAttribute("value", _LDZSHJ),
                                               new XAttribute("sql",""),
                                               new XAttribute("col", colIndex++),
                                               new XAttribute("row", rowIndex),
                                               new XAttribute("rowspan", 1),
                                               new XAttribute("colspan", 1));
                total.Add(cell);

                cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                               new XAttribute("value", _KZSYRS),
                                               new XAttribute("sql", _KZSYRSsql),
                                               new XAttribute("col", colIndex++),
                                               new XAttribute("row", rowIndex),
                                               new XAttribute("rowspan", 1),
                                               new XAttribute("colspan", 1));
                total.Add(cell);

                cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                               new XAttribute("value", _FKZSYRS),
                                               new XAttribute("sql", _FKZSYRSsql),
                                               new XAttribute("col", colIndex++),
                                               new XAttribute("row", rowIndex),
                                               new XAttribute("rowspan", 1),
                                               new XAttribute("colspan", 1));
                total.Add(cell);

                cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                              new XAttribute("value", _LDKQS),
                                              new XAttribute("sql", ""),
                                              new XAttribute("col", colIndex++),
                                              new XAttribute("row", rowIndex),
                                              new XAttribute("rowspan", 1),
                                              new XAttribute("colspan", 1));
                total.Add(cell);

                cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                              new XAttribute("value", _FLDZSHJ),
                                              new XAttribute("sql", ""),
                                              new XAttribute("col", colIndex++),
                                              new XAttribute("row", rowIndex),
                                              new XAttribute("rowspan", 1),
                                              new XAttribute("colspan", 1));
                total.Add(cell);

                cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                              new XAttribute("value", _ZRKYSYRS),
                                              new XAttribute("sql", _ZRKYSYRSsql),
                                              new XAttribute("col", colIndex++),
                                              new XAttribute("row", rowIndex),
                                              new XAttribute("rowspan", 1),
                                              new XAttribute("colspan", 1));
                total.Add(cell);

                cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                              new XAttribute("value", _FZRKYSYRS),
                                              new XAttribute("sql", _FZRKYSYRSsql),
                                              new XAttribute("col", colIndex++),
                                              new XAttribute("row", rowIndex),
                                              new XAttribute("rowspan", 1),
                                              new XAttribute("colspan", 1));
                total.Add(cell);

                cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                              new XAttribute("value", _FLDKQS),
                                              new XAttribute("sql", ""),
                                              new XAttribute("col", colIndex++),
                                              new XAttribute("row", rowIndex),
                                              new XAttribute("rowspan", 1),
                                              new XAttribute("colspan", 1));
                total.Add(cell);

                cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                              new XAttribute("value", _KYSYRS),
                                              new XAttribute("sql",_KYSYRSsql),
                                              new XAttribute("col", colIndex++),
                                              new XAttribute("row", rowIndex),
                                              new XAttribute("rowspan", 1),
                                              new XAttribute("colspan", 1));
                total.Add(cell);

                cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                              new XAttribute("value", _SYQRYSYRS),
                                              new XAttribute("sql", _SYQRYSYRSsql),
                                              new XAttribute("col", colIndex++),
                                              new XAttribute("row", rowIndex),
                                              new XAttribute("rowspan", 1),
                                              new XAttribute("colspan", 1));
                total.Add(cell);

                cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                              new XAttribute("value", _M15N),
                                              new XAttribute("sql", _M15Nsql),
                                              new XAttribute("col", colIndex++),
                                              new XAttribute("row", rowIndex),
                                              new XAttribute("rowspan", 1),
                                              new XAttribute("colspan", 1));
                total.Add(cell);
                cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                              new XAttribute("value", DN1),
                                              new XAttribute("sql", DN1sql),
                                              new XAttribute("col", colIndex++),
                                              new XAttribute("row", rowIndex),
                                              new XAttribute("rowspan", 1),
                                              new XAttribute("colspan", 1));
                total.Add(cell);
                cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                             new XAttribute("value", DN2),
                                             new XAttribute("sql", DN2sql),
                                             new XAttribute("col", colIndex++),
                                             new XAttribute("row", rowIndex),
                                             new XAttribute("rowspan", 1),
                                             new XAttribute("colspan", 1));
                total.Add(cell);

                cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                             new XAttribute("value", DN3),
                                             new XAttribute("sql", DN3sql),
                                             new XAttribute("col", colIndex++),
                                             new XAttribute("row", rowIndex),
                                             new XAttribute("rowspan", 1),
                                             new XAttribute("colspan", 1));
                total.Add(cell);

                cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                              new XAttribute("value", CN1),
                                              new XAttribute("sql", CN1sql),
                                              new XAttribute("col", colIndex++),
                                              new XAttribute("row", rowIndex),
                                              new XAttribute("rowspan", 1),
                                              new XAttribute("colspan", 1));
                total.Add(cell);
                cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                             new XAttribute("value", CN2),
                                             new XAttribute("sql", CN2sql),
                                             new XAttribute("col", colIndex++),
                                             new XAttribute("row", rowIndex),
                                             new XAttribute("rowspan", 1),
                                             new XAttribute("colspan", 1));
                total.Add(cell);

                cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                             new XAttribute("value", CN3),
                                             new XAttribute("sql", CN3sql),
                                             new XAttribute("col", colIndex++),
                                             new XAttribute("row", rowIndex),
                                             new XAttribute("rowspan", 1),
                                             new XAttribute("colspan", 1));
                total.Add(cell);

                cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                             new XAttribute("value", TD1),
                                             new XAttribute("sql", TD1sql),
                                             new XAttribute("col", colIndex++),
                                             new XAttribute("row", rowIndex),
                                             new XAttribute("rowspan", 1),
                                             new XAttribute("colspan", 1));
                total.Add(cell);
                cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                             new XAttribute("value", TD2),
                                             new XAttribute("sql", TD2sql),
                                             new XAttribute("col", colIndex++),
                                             new XAttribute("row", rowIndex),
                                             new XAttribute("rowspan", 1),
                                             new XAttribute("colspan", 1));
                total.Add(cell);

                cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                             new XAttribute("value", TD3),
                                             new XAttribute("sql", TD3sql),
                                             new XAttribute("col", colIndex++),
                                             new XAttribute("row", rowIndex),
                                             new XAttribute("rowspan", 1),
                                             new XAttribute("colspan", 1));
                total.Add(cell);

                rows.Add(total);
                rowIndex++;
                #endregion
            }
            #endregion

            #region 最后一行
            row = new XElement("row", new XAttribute("rowindex", rowIndex));
            colIndex = 0;
            cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                               new XAttribute("value", "特殊情况说明"),
                                               new XAttribute("sql", ""),
                                               new XAttribute("col", colIndex),
                                               new XAttribute("row", rowIndex),
                                               new XAttribute("rowspan", 1),
                                               new XAttribute("colspan", 1));


            row.Add(cell);
            colIndex++;

            cell = new XElement("cell", new XAttribute("name", Guid.NewGuid().ToString("N")),
                                               new XAttribute("value", ""),
                                               new XAttribute("sql", ""),
                                               new XAttribute("col", colIndex),
                                               new XAttribute("row", rowIndex),
                                               new XAttribute("rowspan", 1),
                                               new XAttribute("colspan", ColNumbers - 1));
            row.Add(cell);
            colIndex++;

            rows.Add(row);
            rowIndex++;
            #endregion

            root.Add(rows);
            root.Add(new XAttribute("rownumbers", RowNumbers));
            xdoc.Add(root);
            return xdoc.ToString();
        }
    }
}
